﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;

namespace ADO
{
    class Program
    {
        static void Main(string[] args)
        {

            //SqlConnection dbCon = new SqlConnection(Settings1.Default.sqlCon);
            //dbCon.Open();
            //using (dbCon)
            //{
            //    SqlCommand command = new SqlCommand(
            //      "select ProductName,CategoryName from products p join Categories c on p.CategoryID=c.categoryId order by categoryName", dbCon);
            //    SqlDataReader reader = command.ExecuteReader();
            //    using (reader)
            //    {
            //        while (reader.Read())
            //        {
            //            string productName = (string)reader["ProductName"];
            //            string CategoryName = (string)reader["CategoryName"];
            //            Console.WriteLine("{0} - {1} ",
            //              CategoryName, productName);
            //        }
            //    }
            //}

            AddProduct("6okolad",2,4,null,null,34,null,2,true);

        }


        static void AddProduct(string productName,int? supplierID,int? categoryID,string quantityPerUnit,double? unitPrice,int? unitsInStock,int? unitsOnOrder,int? reorderLevel,bool discontinued)
        {
            SqlConnection dbCon = new SqlConnection(Settings1.Default.sqlCon);
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand(
                  "insert into products (ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) values (@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued)", dbCon);
                cmd.Parameters.Add(new SqlParameter("@ProductName", productName));
                SqlParameter sqlParameterSupplierID = new SqlParameter("@SupplierID", supplierID);
                if (supplierID == null)
                {
                    sqlParameterSupplierID.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterSupplierID);
                SqlParameter sqlParameterCategoryID = new SqlParameter("@CategoryID", categoryID);
                if (categoryID == null)
                {
                    sqlParameterCategoryID.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterCategoryID);
                SqlParameter sqlParameterQuantityPerUnit = new SqlParameter("@QuantityPerUnit", quantityPerUnit);
                if (quantityPerUnit == null)
                {
                    sqlParameterQuantityPerUnit.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterQuantityPerUnit);

                SqlParameter sqlParameterUnitPrice = new SqlParameter("@UnitPrice", unitPrice);
                if (unitPrice == null)
                {
                    sqlParameterUnitPrice.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterUnitPrice);

                SqlParameter sqlParameterUnitsInStock = new SqlParameter("@UnitsInStock", unitsInStock);
                if (unitsInStock == null)
                {
                    sqlParameterUnitsInStock.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterUnitsInStock);

                SqlParameter sqlParameterUnitsOnOrder = new SqlParameter("@UnitsOnOrder", unitsOnOrder);
                if (unitsOnOrder == null)
                {
                    sqlParameterUnitsOnOrder.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterUnitsOnOrder);

                SqlParameter sqlParameterReorderLevel = new SqlParameter("@ReorderLevel", reorderLevel);
                if (unitsOnOrder == null)
                {
                    sqlParameterReorderLevel.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterReorderLevel);
                cmd.Parameters.Add(new SqlParameter("@discontinued", discontinued));
                int p = cmd.ExecuteNonQuery();
                Console.WriteLine(p+ " products are inserted.");
            }
        }

    }
}
